﻿CREATE TABLE [lov].[Field] (
    [Id]          INT           IDENTITY (1, 1) NOT NULL,
    [EntityId]    INT           NOT NULL,
    [Name]        VARCHAR (255) NOT NULL,
    [DataType]    VARCHAR (100) NOT NULL,
    [Description] VARCHAR (255) NULL,
    [SortOrder]   TINYINT       NULL,
    [Nullable]    BIT           NULL,
    [PrimaryKey]  BIT           NULL,
    CONSTRAINT [PK_LOVName] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 90),
    CONSTRAINT [FK_Field_Entity] FOREIGN KEY ([EntityId]) REFERENCES [lov].[Entity] ([Id])
);




GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_Guard]
    ON [lov].[Field]([EntityId] ASC, [Name] ASC)
    INCLUDE([Description], [DataType], [SortOrder], [Nullable]) WITH (IGNORE_DUP_KEY = ON);


GO

/*=============================================
Author:		Andrew Butenko
Create date:	2012-02-03.
Description:	updated entity view for modified/new entity 
=============================================*/
CREATE TRIGGER [lov].[iuTrig_Field] ON [lov].[Field]
AFTER INSERT, UPDATE, DELETE AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @EntityId  INT
	SELECT @EntityId = EntityId FROM inserted
	IF @EntityId IS NULL
		SELECT @EntityId = EntityId FROM deleted

	EXEC [lov].[usp_GenerateEntities] Null, @EntityId, 2 --execute only
END